package com.zd.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

/**
 * excel工具类
 * 
 * @author ZL
 *
 */
public class ExcelUtil {

	public static void main(String[] args) throws Exception {

		String sheetName = "用车统计表单";
		String titleName = "用车申请数据统计表";
		String fileName = "用车申请统计表单";

		String[][] dataList = { { "001", "2015-01-01", "IT" },
				{ "002", "2015-01-02", "市场部" }, { "003", "2015-01-03", "测试" } };
		String[] columnName = { "单号", "申请时间", "申请部门" };
		localExcel(titleName,columnName, dataList,"D:\\123.xls");
	}

	/**
	 * 直接下载导出
	 * @param titleName
	 * 				标题
	 * @param columnName
	 * 				列名
	 * @param dataList
	 * 				内容
	 * @param fileName
	 * 				文件名要求有后缀
	 * @param response
	 * @throws Exception
	 */
	public static void exportWithResponse(String titleName, String[] columnName, String[][] dataList,String fileName, HttpServletResponse response) throws Exception {

		HSSFWorkbook wb = getExcel(titleName, columnName, dataList);

		response.setContentType("application/ms-excel;charset=UTF-8");
			response.setHeader("Content-Disposition", "attachment;filename="
					.concat(String.valueOf(URLEncoder.encode(fileName, "UTF-8"))));
			OutputStream out = response.getOutputStream();
			try {
				wb.write(out);// 将数据写出去

			} catch (Exception e) {
				e.printStackTrace();

			} finally {
				out.close();
			}


	}


	/**
	 * 本地存文件
	 * @param titleName
	 * 				标题
	 * @param columnName
	 * 				列名
	 * @param dataList
	 * 				内容
	 * @param filePath
	 * 				文件完整路径，包括文件名
	 * @throws Exception
	 */
	public static void localExcel(String titleName, String[] columnName, String[][] dataList,String filePath) throws Exception {


		HSSFWorkbook wb = getExcel(titleName, columnName, dataList);

		try {
				FileOutputStream fout = new FileOutputStream(filePath);
				wb.write(fout);
				fout.close();
			} catch (Exception e) {
				e.printStackTrace();

			}


	}


	/**
	 *
	 * 获得excel
	 * @param titleName
	 * 				标题
	 * @param columnName
	 * 				列头
	 * @param dataList
	 *				内容
	 * @return
	 */
	private static HSSFWorkbook getExcel(String titleName, String[] columnName, String[][] dataList){

		//列数
		int columnNumber=columnName.length;

		// 第一步，创建一个webbook，对应一个Excel文件
		HSSFWorkbook wb = new HSSFWorkbook();
		// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet();
		// sheet.setDefaultColumnWidth(15); //统一设置列宽
		for (int i = 0; i < columnNumber; i++)
		{

			for (int j = 0; j <= i; j++)
			{
				if (i == j)
				{
					sheet.setColumnWidth(i, columnName[j].getBytes().length * 256); // 根据文字长度自动设置列宽
				}
			}
		}
		// 创建第0行 也就是标题
		HSSFRow row1 = sheet.createRow((int) 0);
		row1.setHeightInPoints(50);// 设备标题的高度
		// 第三步创建标题的单元格样式style2以及字体样式headerFont1
		HSSFCellStyle style2 = wb.createCellStyle();
		style2.setAlignment(HorizontalAlignment.CENTER);
		style2.setVerticalAlignment(VerticalAlignment.CENTER);
		style2.setFillForegroundColor(HSSFColor.WHITE.index);
		style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		HSSFFont headerFont1 = (HSSFFont) wb.createFont(); // 创建字体样式
		headerFont1.setBold(true); // 字体加粗
		headerFont1.setFontName("黑体"); // 设置字体类型
		headerFont1.setFontHeightInPoints((short) 15); // 设置字体大小
		style2.setFont(headerFont1); // 为标题样式设置字体样式

		HSSFCell cell1 = row1.createCell(0);// 创建标题第一列
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
				columnNumber - 1)); // 合并第0到第17列
		cell1.setCellValue(titleName); // 设置值标题
		cell1.setCellStyle(style2); // 设置标题样式

		// 创建第1行 也就是表头
		HSSFRow row = sheet.createRow((int) 1);
		row.setHeightInPoints(37);// 设置表头高度

		// 第四步，创建表头单元格样式 以及表头的字体样式
		HSSFCellStyle style = wb.createCellStyle();
		style.setWrapText(true);// 设置自动换行
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER); // 创建一个居中格式

		style.setBottomBorderColor(HSSFColor.BLACK.index);
		style.setBorderBottom(BorderStyle.THIN);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		style.setBorderTop(BorderStyle.THIN);

		HSSFFont headerFont = (HSSFFont) wb.createFont(); // 创建字体样式
		headerFont.setBold(true); // 字体加粗
		headerFont.setFontName("黑体"); // 设置字体类型
		headerFont.setFontHeightInPoints((short) 10); // 设置字体大小
		style.setFont(headerFont); // 为标题样式设置字体样式

		// 第四.一步，创建表头的列
		for (int i = 0; i < columnNumber; i++)
		{
			HSSFCell cell = row.createCell(i);
			cell.setCellValue(columnName[i]);
			cell.setCellStyle(style);
		}

		// 第五步，创建单元格，并设置值
		for (int i = 0; i < dataList.length; i++)
		{
			row = sheet.createRow((int) i + 2);
			// 为数据内容设置特点新单元格样式1 自动换行 上下居中
			HSSFCellStyle zidonghuanhang = wb.createCellStyle();
			zidonghuanhang.setWrapText(true);// 设置自动换行
			zidonghuanhang
					.setVerticalAlignment(VerticalAlignment.CENTER); // 创建一个居中格式

			// 设置边框
			zidonghuanhang.setBottomBorderColor(HSSFColor.BLACK.index);
			zidonghuanhang.setBorderBottom(BorderStyle.THIN);
			zidonghuanhang.setBorderLeft(BorderStyle.THIN);
			zidonghuanhang.setBorderRight(BorderStyle.THIN);
			zidonghuanhang.setBorderTop(BorderStyle.THIN);

			// 为数据内容设置特点新单元格样式2 自动换行 上下居中左右也居中
			HSSFCellStyle zidonghuanhang2 = wb.createCellStyle();
			zidonghuanhang2.setWrapText(true);// 设置自动换行
			zidonghuanhang2
					.setVerticalAlignment(VerticalAlignment.CENTER); // 创建一个上下居中格式
			zidonghuanhang2.setAlignment(HorizontalAlignment.CENTER);// 左右居中

			// 设置边框
			zidonghuanhang2.setBottomBorderColor(HSSFColor.BLACK.index);
			zidonghuanhang2.setBorderBottom(BorderStyle.THIN);
			zidonghuanhang2.setBorderLeft(BorderStyle.THIN);
			zidonghuanhang2.setBorderRight(BorderStyle.THIN);
			zidonghuanhang2.setBorderTop(BorderStyle.THIN);
			HSSFCell datacell = null;
			for (int j = 0; j < columnNumber; j++)
			{
				datacell = row.createCell(j);
				datacell.setCellValue(dataList[i][j]);
				datacell.setCellStyle(zidonghuanhang2);
			}
		}
		return wb;


	}

}
